/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.elcom.emocbc.dbmapping.persistence;

import com.elcom.emocbc.dbmapping.tables.daos.ProgramsDao;
import com.elcom.emocbc.dbmapping.tables.pojos.Programs;
import com.elcom.emocbc.util.IntegerResultSetHandler;
import com.elcom.emocbc.util.StringListResultSetHandler;
import com.google.common.collect.ArrayListMultimap;
import com.google.common.collect.Maps;
import com.google.common.collect.Multimap;
import com.google.common.collect.Multimaps;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.jooq.Configuration;
import org.jooq.ConnectionProvider;
import org.jooq.SQLDialect;
import org.jooq.conf.Settings;
import org.jooq.conf.StatementType;
import org.jooq.impl.DefaultConfiguration;

/**
 *
 * @author GiangLT
 */
public class ProgramPersistance extends ProgramsDao {

    public ProgramPersistance() {
        ConnectionProvider connectionProvider = new DbConnectionProvider();
        Settings settings = new Settings();
        settings.setStatementType(StatementType.STATIC_STATEMENT);
        Configuration configuration = new DefaultConfiguration()
                .set(connectionProvider)
                .set(SQLDialect.ORACLE)
                .set(settings);
        this.setConfiguration(configuration);
    }

    public List<Programs> findByProgramId(String programId) throws SQLException {
        Connection con = DbAccess.getInstance().getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT * "
                + " FROM programs "
                + " WHERE program_id = ? ";
        try {
            return qr.query(con, sql, new ProgramListHandler(), programId);
        } finally {
            DbUtils.closeQuietly(con);
        }
    }

    public List<Programs> findApprovedByCpTopicIdAndIndexInDay(Long cpTopicId, Integer programIndex) throws SQLException {
        Connection con = DbAccess.getInstance().getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT * "
                + " FROM programs "
                + " WHERE trunc(timeplug) = trunc(sysdate)"
                + "      AND cps_topic_id = ? "
                + "      AND indexs = ? "
                + "      AND status = 1 ";
        try {
            return qr.query(con, sql, new ProgramListHandler(), cpTopicId, programIndex);
        } finally {
            DbUtils.closeQuietly(con);
        }
    }

    public List<String> findProgramIdsByTopic(int topicId) throws SQLException {
        Connection con = DbAccess.getInstance().getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT p.program_id "
                + " FROM programs p "
                + " INNER JOIN cp_topic cpt ON p.cps_topic_id = cpt.cp_topic_id "
                + " WHERE cpt.topic_id = ? "
                + " AND p.status = 1 "
                + " AND TRUNC(timeplug) = TRUNC(SYSDATE) ";
        try {
            return qr.query(con, sql, new StringListResultSetHandler(), topicId);
        } finally {
            DbUtils.closeQuietly(con);
        }
    }

    public Integer findTopicId(int cpTopicId) throws SQLException {
        Connection con = DbAccess.getInstance().getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT p.topic_id "
                + " FROM cp_topic p "
                + " WHERE p.cp_topic_id = ? ";
        try {
            return qr.query(con, sql, new IntegerResultSetHandler(), cpTopicId);
        } finally {
            DbUtils.closeQuietly(con);
        }
    }

    public Map<String, Integer> countBroadcastedProgramIdsInDay(int topicId, long zoneId) throws SQLException {
        ResultSetHandler<Map<String, Integer>> resultSetHandler = new ResultSetHandler<Map<String, Integer>>() {

            @Override
            public Map<String, Integer> handle(ResultSet rs) throws SQLException {
                Map<String, Integer> result = Maps.newHashMap();
                while (rs.next()) {
                    result.put(rs.getString(1), rs.getInt(2));
                }
                return result;
            }
        };
        Connection con = DbAccess.getInstance().getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT program_id, count(*) "
                + " FROM broadcast_hist "
                + " WHERE topic_id = ? "
                + " AND zone_id = ? "
                + " AND TRUNC(starttime) = TRUNC(SYSDATE) "
                + " GROUP BY program_id";
        try {
            return qr.query(con, sql, resultSetHandler, topicId, zoneId);
        } finally {
            DbUtils.closeQuietly(con);
        }
    }

    public static void main(String[] args) {
        Map<String, Integer> map1 = new HashMap<String, Integer>();
        map1.put("a", 0);
        map1.put("b", 0);
        map1.put("c", 0);
        map1.put("d", 0);
        map1.put("e", 0);
        map1.put("f", 0);
        map1.put("g", 0);
        map1.put("h", 0);
        map1.put("i", 0);
        map1.put("j", 0);
        map1.put("k", 0);

        Map<String, Integer> map2 = new HashMap<String, Integer>();
        map2.put("a", 2);
        map2.put("b", 3);
        map2.put("c", 2);
        map2.put("d", 2);
        map2.put("e", 1);
        map2.put("f", 1);
        map2.put("g", 1);

        map1.putAll(map2);

        for (String key : map1.keySet()) {
            System.out.println(key + ": " + map1.get(key));
        }

        Multimap<Integer, String> multimap = ArrayListMultimap.create();
        Multimaps.invertFrom(Multimaps.forMap(map1), multimap);

        for (Integer key : multimap.keySet()) {
            Collection<String> values = multimap.get(key);
            System.out.println(key + ":" + values);
        }

    }

    private class ProgramListHandler implements ResultSetHandler<List<Programs>> {

        @Override
        public List<Programs> handle(ResultSet rs) throws SQLException {
            List<Programs> programs = new ArrayList<Programs>();
            int i = 1;
            while (rs.next()) {
                Programs program = new Programs();
                program.setProgramId(rs.getString(i++));
                program.setTimeplug(rs.getTimestamp(i++));
                program.setSubcontents(rs.getString(i++));
                program.setCpsTopicId(BigInteger.valueOf(rs.getInt(i++)));
                program.setStatus(rs.getInt(i++));
                program.setIndexs(BigInteger.valueOf(rs.getInt(i++)));
                program.setDurationUpdate(rs.getString(i++));
                program.setType(rs.getInt(i++));
                program.setInsertTime(rs.getTimestamp(i++));
                program.setChainId(rs.getString(i++));
                program.setCorrectanswer(rs.getInt(i++));
                programs.add(program);
            }
            return programs;
        }
    }

}
